package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.Employee;
import com.kaishengit.entity.FileType;
import com.kaishengit.entity.Project;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;

public class FileTypeDao {
	private DBHelp<FileType> db = new DBHelp<FileType>();
	public List<FileType> findByProjectId(String projectid) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT tft.id,tft.name,tft.createtime,tft.employeeid,tft.projectid,tft.desc,");
		sql.append("(SELECT SUM(filesize) FROM t_file WHERE filetypeid = tft.id) AS totalsize,");
		sql.append("(SELECT COUNT(filetypeid) FROM t_file WHERE filetypeid = tft.id) AS num,");
		sql.append("te.username,te.password,te.email,te.pic,tp.name AS pname,tp.desc AS pdesc,tp.createtime AS ptime ");
		sql.append("FROM t_filetype AS tft LEFT JOIN t_employee AS te ON tft.employeeid = te.id ");
		sql.append("LEFT JOIN t_project AS tp ON tp.id = tft.projectid ");
		sql.append("WHERE projectid = ?");
		return db.executeQueryForList(sql.toString(), new FileTypeRowMapper(),projectid);
	}
	public boolean save(String id, String name, String desc, String createtime,
			String employeeid, String projectid) {
		String sql = "insert into t_filetype(id,`name`,`desc`,createtime,employeeid,projectid) values(?,?,?,?,?,?)";
		return db.executeSQL(sql, id,name,desc,createtime,employeeid,projectid);
	}


	
	
	
	private class FileTypeRowMapper implements RowMapper<FileType>{

		public FileType mapRow(ResultSet rs) throws SQLException {
			FileType f = new FileType();
			f.setCreatetime(rs.getString("createtime"));
			f.setDesc(rs.getString("desc"));
			f.setId(rs.getString("id"));
			f.setName(rs.getString("name"));
			f.setNum(rs.getInt("num"));
			f.setTotalsize(rs.getFloat("totalsize"));
			f.setEmployeeid(rs.getString("employeeid"));
			f.setProjectid(rs.getString("projectid"));
			Employee e = new Employee();
			e.setEmail(rs.getString("email"));
			e.setId(rs.getString("employeeid"));
			e.setPassword(rs.getString("password"));
			e.setPic(rs.getString("pic"));
			e.setUsername(rs.getString("username"));
			f.setEmployee(e);
			Project p = new Project();
			p.setCreatetime(rs.getString("ptime"));
			p.setDesc(rs.getString("pdesc"));
			p.setId(rs.getString("projectid"));
			p.setName(rs.getString("pname"));
			f.setProject(p);
			return f;
		}
		
	}





	
}
